package nix.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Test1 {

	@Test
	public void fun1() throws ClassNotFoundException, SQLException {
		// 加载驱动类（注册驱动类）
		Class.forName("com.mysql.jdbc.Driver");
		// 使用url、username、password得到连接对象
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/test", "root", "race");

		System.out.println(con);
	}

	@Test
	public void fun2() throws ClassNotFoundException, SQLException {

		String driverName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "race";

		Class.forName(driverName);
		Connection con = DriverManager.getConnection(url, username, password);
		// 创建Statement对象
		Statement stmt = con.createStatement();
		// SQL语句
		String sql = "insert into stu (id,name) values (1,'zhangSan')";
		// String sql = "update stu set name = 'liSi' where id = 1";
		// String sql = "delete from stu";
		// 执行
		int r = stmt.executeUpdate(sql);
		System.out.println(r);
	}

	@Test
	public void fun3() throws ClassNotFoundException, SQLException {

		String driverName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "race";

		Class.forName(driverName);
		Connection con = DriverManager.getConnection(url, username, password);
		// 创建Statement对象
		Statement stmt = con.createStatement();
		// SQL语句
		String sql = "select * from stu";
		// 执行
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			System.out.println(id + ", " + name);
		}

		// 关闭资源
		rs.close();
		stmt.close();
		con.close();
	}

	@Test
	public void fun4() throws Exception {

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/test";
			String username = "root";
			String password = "race";

			Class.forName(driverName);
			con = DriverManager.getConnection(url, username, password);

			stmt = con.createStatement();
			String sql = "select * from stu";
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				System.out.println(rs.getString("id") + ", "
						+ rs.getString("name"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (con != null)
				con.close();
		}
	}

	@Test
	public void fun5() throws Exception {

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/test";
			String username = "root";
			String password = "race";

			Class.forName(driverName);
			con = DriverManager.getConnection(url, username, password);

			stmt = con.createStatement();
			String sql = "select * from stu";
			rs = stmt.executeQuery(sql);

			// 获取列数（获取元数据getMetaData()、获取列名getColumnName(int colIndex)）
			int count = rs.getMetaData().getColumnCount();
			while (rs.next()) {

				for (int i = 1; i <= count; i++) {
					System.out.print(rs.getString(i));
					if (i != count) {
						System.out.print(", ");
					}
				}
				System.out.println();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (con != null)
				con.close();
		}
	}

	@Test
	public void fun6() throws Exception {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JdbcUtils.getConnection();

			// 参数赋值
			String sql = "select * from stu where id = ? and name = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, 1);
			pstmt.setString(2, "zhangSan");

			rs = pstmt.executeQuery();

			// 获取列数（获取元数据getMetaData()、获取列名getColumnName(int colIndex)）
			int count = rs.getMetaData().getColumnCount();
			while (rs.next()) {

				for (int i = 1; i <= count; i++) {
					System.out.print(rs.getString(i));
					if (i != count) {
						System.out.print(", ");
					}
				}
				System.out.println();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (con != null)
				con.close();
		}
	}

	@Test
	public void fun7() throws ClassNotFoundException, SQLException {

		String driverName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
		String username = "root";
		String password = "race";

		Class.forName(driverName);
		Connection con = DriverManager.getConnection(url, username, password);
		// SQL语句
		String sql = "insert into stu (id,name) values (?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);

		for (int i = 0; i < 10000; i++) {
			pstmt.setInt(1, i + 1);
			pstmt.setString(2, "stu_" + (i + 1));
			// 添加批处理
			pstmt.addBatch();
		}
		// 执行批处理
		pstmt.executeBatch();
	}
}
